import pymysql
import os

class AdminManager:
    def __init__(self):
        self.mysql_pwd = os.getenv('MYSQL_PWD', "123456")
        self.db_name = os.getenv('DB_NAME', "appDB")

    def get_db_connection(self):
        """获取数据库连接"""
        return pymysql.connect(
            host="localhost",
            user="root",
            password=self.mysql_pwd,
            database=self.db_name,
            charset='utf8'
        )

    def get_restaurant_list(self):
        """获取餐厅列表"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            sql = "SELECT * FROM RESTAURANT"
            cursor.execute(sql)
            res = cursor.fetchall()
            return res if res else None
        except Exception as e:
            print(f"Error: {e}")
            return None
        finally:
            if db:
                db.close()

    def delete_restaurant(self, rest_name):
        """删除餐厅及其相关数据"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()

            # 删除 DISHES 表中的相关记录
            sql1 = "DELETE FROM DISHES WHERE restaurant = %s"
            cursor.execute(sql1, (rest_name,))
            db.commit()

            # 删除 ORDER_COMMENT 表中的相关记录
            sql2 = "DELETE FROM ORDER_COMMENT WHERE restaurant = %s"
            cursor.execute(sql2, (rest_name,))
            db.commit()

            # 删除 SHOPPINGCART 表中的相关记录
            sql3 = "DELETE FROM SHOPPINGCART WHERE restaurant = %s"
            cursor.execute(sql3, (rest_name,))
            db.commit()

            # 删除 RESTAURANT 表中的记录
            sql4 = "DELETE FROM RESTAURANT WHERE username = %s"
            cursor.execute(sql4, (rest_name,))
            db.commit()

            return True
        except Exception as e:
            print(f"Error: {e}")
            return False
        finally:
            if db:
                db.close()

    def get_comment_list(self):
        """获取评论列表"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            sql = "SELECT * FROM ORDER_COMMENT WHERE isFinished = 1 AND text <> ''"
            cursor.execute(sql)
            res = cursor.fetchall()
            return res if res else None
        except Exception as e:
            print(f"Error: {e}")
            return None
        finally:
            if db:
                db.close()

    def get_sorted_comments(self):
        """按评分升序排列评论"""
        db = None
        try:
            db = self.get_db_connection()
            cursor = db.cursor()
            sql = "SELECT * FROM ORDER_COMMENT WHERE isFinished = 1 AND text IS NOT NULL ORDER BY c_rank"
            cursor.execute(sql)
            res = cursor.fetchall()
            return res if res else None
        except Exception as e:
            print(f"Error: {e}")
            return None
        finally:
            if db:
                db.close()